package com.infore.dataReceiving.repository;
import java.util.List;

import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.JpaSpecificationExecutor;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.query.Param;
import org.springframework.stereotype.Repository;

import com.infore.dataReceiving.entity.BackControlLog;

@Repository
public interface IBackControlLogRepository extends JpaRepository<BackControlLog, String>, JpaSpecificationExecutor<BackControlLog>  {
	
	@Query(nativeQuery = true,value = "UPDATE BACKCONTROL_LOG SET RESPONSE =:response WHERE MN=:mn AND QN=:qn")
	public BackControlLog updateResponseFlag(@Param("response")String response,@Param("mn")String mn,@Param("qn")String qn);
	
	
	@Query(nativeQuery = true,value = "UPDATE BACKCONTROL_LOG SET RESULT =:result WHERE MN=:mn AND QN=:qn")
	public BackControlLog updateResultFlag(@Param("result")String result,@Param("mn")String mn,@Param("qn")String qn);
	
	
	@Query(nativeQuery = true,value = "select * from ("+
			"SELECT rownum no,log.* FROM BACKCONTROL_LOG log where mn=:mn "
			+ "AND OPERATION_TIME  "
			+ "between to_date(:startTime,'yyyy-MM-dd HH24:mi:ss') and to_date(:endTime,'yyyy-MM-dd HH24:mi:ss') "
			+ "AND rownum<=:endSize "
			+ "ORDER BY OPERATION_TIME DESC"
			+") table_alias WHERE table_alias.no >=:startSize")
	public List<BackControlLog> queryBackControlLog(@Param("mn")String mn,@Param("startTime")String startTime,
			@Param("endTime")String endTime,@Param("endSize")int endSize,@Param("startSize")int startSize);
	
	
	/**
	 * @param stationList
	 * @return
	 */
	@Query(nativeQuery=true,value="select count(*) sl from("+
			"SELECT log.* FROM BACKCONTROL_LOG log where mn=:mn "
			+ "AND OPERATION_TIME  "
			+ "between to_date(:startTime,'yyyy-MM-dd HH24:mi:ss') and to_date(:endTime,'yyyy-MM-dd HH24:mi:ss') "
			+ "ORDER BY OPERATION_TIME DESC) aa")
	public Integer findBackControlLogCount(String mn,String startTime,String endTime);


}
